﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Configuration;


namespace spring.stp6.db.provider
{
	/// <summary>
	/// Represents a set of methods to get data from the db and convert into app entities
	/// </summary>
	public class BaseProvider
	{		
        private static ConnectionStringSettings _connectionString;
        public static  ConnectionStringSettings ConnectionString
        {
            get
            {
                if (_connectionString == null)
                {
                    //Key used in connectionStrings dictionary
                    const string _connectionKey = "db";
                    //Default provider
                    var providerName = "System.Data.SqlClient";
                    ConnectionStringSettings conn = ConfigurationManager.ConnectionStrings[_connectionKey];

                    if (conn == null)
                    {
                        throw new ConfigurationErrorsException("You must specify a SQL Connection string in the configuration file, with the key " + _connectionKey + ".");
                    }

                    if (!String.IsNullOrEmpty(conn.ProviderName))
                    {
                        providerName = conn.ProviderName;
                    }
                    _connectionString = new ConnectionStringSettings(_connectionKey, conn.ConnectionString, providerName);
                }
                return _connectionString;
            }
        }

        private static string _parameterPrefix;
        public static string ParameterPrefix
        {
            get
            {
                if (ConnectionString.ProviderName.ToUpper() == "SYSTEM.DATA.SQLCLIENT")
                {
                    _parameterPrefix = "@";

                }
                else if (ConnectionString.ProviderName.ToUpper() == "MYSQL.DATA.MYSQLCLIENT")
                {
                    _parameterPrefix = "param_";
                }
                return _parameterPrefix;
            }
        }

        public BaseProvider()
		{
			this.Factory = DbProviderFactories.GetFactory(ConnectionString.ProviderName);
		}

		/// <summary>
		/// Gets a new connection.
		/// </summary>
		/// <returns></returns>
		public virtual DbConnection GetConnection()
		{
			DbConnection conn = this.Factory.CreateConnection();
            conn.ConnectionString = ConnectionString.ConnectionString;
			return conn;
		}

		/// <summary>
		/// The database provider factory to create the connections and commands to access the db.
		/// </summary>
		protected DbProviderFactory Factory
		{
			get;
			set;
		}

		/// <summary>
		/// Gets a new command for procedure executing.
		/// </summary>
		/// <param name="procedureName"></param>
		/// <returns></returns>
		protected DbCommand GetCommand(string procedureName)
		{
			DbCommand comm = this.Factory.CreateCommand();
			comm.Connection = GetConnection();
			comm.CommandText = procedureName;
			comm.CommandType = CommandType.StoredProcedure;
			return comm;
		}

		/// <summary>
		/// Gets a datatable filled with the results of executing the command.
		/// </summary>
		protected DataTable GetTable(DbCommand command)
		{
			var dt = new DataTable();
			var da = this.Factory.CreateDataAdapter();
			da.SelectCommand = command;
			da.Fill(dt);

			return dt;
		}

		/// <summary>
		/// Gets a datatable filled with the first result of executing the command.
		/// </summary>
		protected DataRow GetFirstRow(DbCommand command)
		{
			DataRow dr = null;
			DataTable dt = GetTable(command);
			if (dt.Rows.Count > 0)
			{
				dr = dt.Rows[0];
			}
			return dr;
		}

		/// <summary>
		/// Disposes the reader.
		/// </summary>
		/// <param name="reader"></param>
		protected void SafeDispose(DbDataReader reader)
		{
			if (reader != null)
			{
				reader.Dispose();
			}
		}

		/// <summary>
		/// Safely opens the connection, executes and closes the connection
		/// </summary>
		/// <param name="comm"></param>
		/// <returns></returns>
		protected int SafeExecuteNonQuery(DbCommand comm)
		{
			return comm.SafeExecuteNonQuery();
		}
	}
}
